//sqlite相关操作
import {app} from "electron";
import {Database} from "sqlite3";
import path from "path";
import * as fs from "node:fs";

const isPackaged = app.isPackaged;//是否已打包
const basePath = isPackaged ? app.getPath("userData") : __dirname;
const dbPath = path.join(basePath, "datas");
//确保数据目录存在
if (!fs.existsSync(dbPath)) {
	fs.mkdirSync(dbPath, {recursive: true});
}
const dbFile = path.join(dbPath, "sqlite.db");
const sourceFile = isPackaged ? path.resolve(app.getAppPath(), '../../src/main/db/sqlite.db') : path.join(__dirname, '../../../src/main/db/sqlite.db');
// console.log(app.getAppPath(),sourceFile)
// 如果数据库文件不存在，则从资源中复制它
if (!fs.existsSync(dbFile)) {
	fs.copyFileSync(sourceFile, dbFile);
}

export class SqliteDb {
	db;

	constructor() {
		this.db = new Database(dbFile);
	}

	/**
	 * 测试数据库，添加一张表、写入点内容
	 */
	test() {
		this.db.serialize(() => {
			this.db.run("CREATE TABLE testSqlite (info TEXT)");

			const stmt = this.db.prepare("INSERT INTO testSqlite VALUES (?)");
			for (let i = 0; i < 10; i++) {
				stmt.run("Ipsum " + i);
			}
			stmt.finalize();

			this.db.each(
				"SELECT rowid AS id, info FROM testSqlite",
				(err: any, row: any) => {
					if (!err) console.log(row.id + ": " + row.info);
				}
			);
		});
		this.db.close();
	}

	/**
	 * 检查数据表是否存在
	 */
	table_exists(tableName: string): Promise<boolean> {
		const sql = "SELECT name FROM sqlite_master WHERE type='table' AND name=?";
		const params = [tableName];
		let mydb = this.db;
		return new Promise((resolve, reject) => {
			mydb.get(sql, params, (err: any, row: any) => {
				console.log("查表执行结果：", err, row);
				if (err) {
					reject(err);
					resolve(false);
				} else {
					resolve(true);
				}
			});
		});
	}

	/**
	 * 执行sql,异步执行的
	 */
	async run(sql: string, params: string[]) {
		let promise = new Promise((resolve, reject) => {
			this.db.get(sql, params, (err: any, row: any) => {
				console.log("SQL语句执行结果：", err, row);
				if (err) reject(err);
				resolve(row);
			});
		});
		let res = await promise;
		// console.log(res);
		return res;
	}

	/**
	 * 关闭数据库
	 */
	close() {
		this.db.close();
	}
}
